package com.ndp.fb.rdb.db.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ndp.fb.constant.CommonConstants;
import com.ndp.fb.rdb.api.AdRdbService;
import com.ndp.fb.rdb.api.ProductConfigurationRdbService;
import com.ndp.fb.rdb.api.VpiInfoRdbService;
import com.ndp.fb.rdb.db.DaoTemplate;
import com.ndp.fb.rdb.db.impl.mapper.DefaultRowMapper;
import com.ndp.fb.rdb.model.AdGroup;
import com.ndp.fb.rdb.model.AdGroupExt;
import com.ndp.fb.rdb.model.ProductConfiguration;
import com.ndp.fb.rdb.model.VpiCountryInfo;
import com.ndp.fb.rdb.model.VpiInfo;
import com.ndp.fb.redis.api.VpiInfoRedisService;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by Sisi on 15-2-27.
 * 写更新语句时记得更新数据的最后更新时间
 * 更新完数据库时记得同步更新redis缓存
 */
@Service
public class AdRdbServiceImpl extends DaoTemplate implements AdRdbService {
    private static Logger logger = LoggerFactory.getLogger(AdRdbServiceImpl.class);


    @Autowired
    private VpiInfoRedisService vpiInfoRedisService;
    @Autowired
    private VpiInfoRdbService vpiInfoRdbService;
    @Autowired
    private ProductConfigurationRdbService productConfigurationRdbService;

    @Override
    public Long save(final AdGroup adgroup) {
        return saveResultId(adgroup);
//        String sql = "INSERT INTO ad_group " +
//                "(ad_group_id, campaign_group_id, ad_set_id, fb_account_id, fb_connection_object_id," +
//                " publish_batch_id, product_conf_id, ad_creative_id,name,adgroup_status," +
//                " bid_info,bid_type,conversion_specs,tracking_specs,updated_time," +
//                " created_time,objective,updated_utc_time,created_utc_time,local_updated_time,bid_amount,failed_delivery_checks)" +
//                " VALUES" +
//                " (?,?,?,?,?," +
//                " ?,?,?,?,?," +
//                " ?,?,?,?,?," +
//                " ?,?,?,?,?,?,?)";
//        return saveResultId(sql, new PstAssign() {
//            @Override
//            public void setParam(PreparedStatement ps) throws SQLException {
//                ps.setObject(1, adgroup.getAdGroupId());
//                ps.setObject(2, adgroup.getCampaignGroupId());
//                ps.setObject(3, adgroup.getAdSetId());
//                ps.setObject(4, adgroup.getFbAccountId());
//                ps.setObject(5, adgroup.getFbConnectionObjectId());
//                ps.setObject(6, adgroup.getPublishBatchId());
//                ps.setObject(7, adgroup.getProductConfId());
//                ps.setObject(8, adgroup.getAdCreativeId());
//                ps.setObject(9, adgroup.getName());
//                ps.setObject(10, adgroup.getAdGroupStatus());
//                ps.setObject(11, adgroup.getBidInfo());
//                ps.setObject(12, adgroup.getBidType());
//                ps.setObject(13, adgroup.getConversionSpecs());
//                ps.setObject(14, adgroup.getTrackingSpecs());
//                ps.setObject(15, adgroup.getUpdatedTime());
//                ps.setObject(16, adgroup.getCreatedTime());
//                ps.setObject(17, adgroup.getObjective());
//                ps.setObject(18, adgroup.getUpdatedUtcTime());
//                ps.setObject(19, adgroup.getCreatedUtcTime());
//                //增加数据最后被修改时间 giser
//                ps.setObject(20, System.currentTimeMillis());
//                ps.setObject(21, adgroup.getBidAmount());
//                ps.setObject(22, adgroup.getFailed_delivery_checks());
//            }
//        });
    }

    @Override
    public AdGroup findByAdGroupId(Long adGroupId) {
        String sql = "SELECT * FROM ad_group WHERE ad_group_id = ?";
        return findOne(sql, new Object[]{adGroupId}, DefaultRowMapper.autoRowMapper(AdGroup.class));
    }


    @Override
    public List<AdGroup> findByAdSetId(Long adSetId) {
        String sql = "SELECT * FROM ad_group WHERE ad_set_id = ?";
        return getJdbcTemplate().query(sql, new Object[]{adSetId}, DefaultRowMapper.autoRowMapper(AdGroup.class));
    }

    /**
     * 查找单价（vpi），每个转化广告主支付的费用
     *
     * @return
     */
    @Override
    public VpiInfo getPriceNew(Long adGroupId, Long productConfId, Long fbConnectionObjectId, String targeting) {
        //查找 adGroup的应用，平台，国家 信息，用于查找单价
        ////平台
        ProductConfiguration productConfiguration = productConfigurationRdbService.findFromCache(productConfId);
        String platform = null;
        if (productConfiguration != null) {
            platform = productConfiguration.getPlatform();
        }
        return getVpiInfoByPlatForm(productConfId, fbConnectionObjectId, targeting, platform);
    }

    private VpiInfo getVpiInfoByPlatForm(Long productConfId, Long fbConnectionObjectId, String targeting, String platform) {
        VpiInfo result = null;
        if (StringUtils.isBlank(platform) || StringUtils.isBlank(targeting)) {
            logger.info("获取单价失败。" + " platform>>" + platform + " , targeting >> " + targeting);
        } else {
            // 解析国家
            ////{"user_os":["iOS_ver_2.0_and_above"],"geo_locations":{"countries":["CN"]},"age_min":13,"age_max":65,"page_types":["mobile"],"wireless_carrier":["Wifi"],"user_device":["iPhone"]}
            try {
                JSONObject targetingJson = JSONObject.parseObject(targeting);
                if (targetingJson != null) {
                    List<String> countryList = new ArrayList<>();

                    JSONObject geo = targetingJson.getJSONObject("geo_locations");
                    JSONArray countries = null;
                    if (geo != null) {
                        countries = geo.getJSONArray("countries");
                    } else {
                        logger.warn("ad targeting 解析geo_locations失败。");
                    }
                    JSONArray cities = targetingJson.getJSONArray("cities");
                    if (!CollectionUtils.isEmpty(countries)) {
                        for (Object country : countries) {
                            countryList.add(country.toString());
                        }
                    }
                    if (!CollectionUtils.isEmpty(cities)) {
                        for (Object city : cities) {
                            countryList.add(JSONObject.parseObject(city.toString()).getString("country"));
                        }
                    }
                    //查找所有国家，选取最低价。
                    for (Object c : countryList) {
                        String country = c.toString();
//                        logger.info("查找单价缓存，key：" + fbConnectionObjectId + productConfId + country);
                        VpiInfo vpiInfo = vpiInfoRedisService.findCache(String.valueOf(fbConnectionObjectId), productConfId + country);
                        if (vpiInfo == null) {
                            //如果没有获取到缓存，重新查询并更新缓存
                            logger.info("查找单价并更新缓存，key：" + fbConnectionObjectId + productConfId + country);
                            List<VpiInfo> byFbConnObjId = vpiInfoRdbService.findByFbConnObjIdAndPlatform(fbConnectionObjectId, platform);
                            if (byFbConnObjId == null || byFbConnObjId.size() < 1) {
                                logger.info("查找单价并更新缓存,没找到记录。key:" + fbConnectionObjectId + productConfId + country);
                                continue;
                            } else {
                                //缓存单价
                                for (VpiInfo info : byFbConnObjId) {
                                    List<VpiCountryInfo> countryInfos = info.getVpiCountryInfos();
                                    for (VpiCountryInfo countryInfo : countryInfos) {
                                        if (countryInfo.getCountryCode().equals(country)) {
                                            vpiInfo = info;
                                        }
                                        vpiInfoRedisService.saveCache(String.valueOf(fbConnectionObjectId), productConfId + country, info);
                                        logger.info("查找单价并更新缓存,缓存成功。key:" + fbConnectionObjectId + productConfId + country);
                                    }
                                }
                            }
                        }
                        if (vpiInfo != null) {
                            //取最小值
                            if (result == null || result.getVpi() > vpiInfo.getVpi()) {
                                result = vpiInfo;
                            }
                        }
                    }
                }
                if (result == null) {
                    result = vpiInfoRedisService.findCache(String.valueOf(fbConnectionObjectId), productConfId + CommonConstants.GLOBAL);
                }
            } catch (Exception e) {
                logger.error("获取单价失败。" + " key:" + fbConnectionObjectId + "_" + productConfId, e);
                logger.error(targeting);
            }
        }
        return result;
    }


    @Override
    public List<AdGroup> findByFbAccountId(Long fbAccountId, int pIndex, int pSize) {
        String sql = "select * from ad_group where fb_account_id =? limit ? ,?";
        return getJdbcTemplate().query(sql, new Object[]{fbAccountId, pIndex * pSize, pSize}, DefaultRowMapper.autoRowMapper(AdGroup.class));
    }

    @Override
    public List<AdGroupExt> findAdGroupExtByFbAccountId(Long fbAccountId, int pIndex, int pSize) {
        String sql = "SELECT\n" +
                "  ag.*,\n" +
                "  acg.`name` AS campaign_group_name,\n" +
                "  acg.campaign_group_status,\n" +
                "  acg.created_utc_time AS campaign_group_created_utc_time,\n" +
                "  acg.created_time as campaign_group_created_time,\n" +
                "  adset.`name` as adset_name,\n" +
                "  adset.campaign_status adset_status,\n" +
                "  adset.bid_info as adset_bid_info,\n" +
                "  adset.bid_type as adset_bid_type,\n" +
                "  adset.targeting as adset_targeting,\n" +
                "  adset.created_time as adset_created_time,\n" +
                "  adset.created_utc_time as adset_created_utc_time,\n" +
                "  ac.imageMd5,\n" +
                "  ac.image_hash,\n" +
                "  ac.image_url\n" +
                "FROM\n" +
                "  ad_group ag\n" +
                "LEFT JOIN ad_set adset ON ag.ad_set_id = adset.ad_set_id\n" +
                "LEFT JOIN ad_campaign_group acg ON ag.campaign_group_id = acg.campaign_group_id\n" +
                "LEFT JOIN ad_creative ac ON ag.ad_creative_id = ac.ad_creative_id\n" +
                "WHERE\n" +
                "	ag.fb_account_id = ?\n" +
                "LIMIT ?, ?";
        return getJdbcTemplate().query(sql, new Object[]{fbAccountId, pIndex * pSize, pSize}, DefaultRowMapper.autoRowMapper(AdGroupExt.class));
    }

    @Override
    public List<Long> findByFbAccountId(Long fbAccountId) {
        String sql = "select ad_group_id from ad_group where fb_account_id =? ";
        return getJdbcTemplate().queryForList(sql, new Object[]{fbAccountId}, Long.class);
    }

    @Override
    public  List<Long> findByFbConnectObjectId(Long fbConnObjId) {
        String sql = "select ad_group_id from ad_group where fb_connection_object_id =? ";
        return getJdbcTemplate().queryForList(sql, new Object[]{fbConnObjId}, Long.class);
    }

    @Override
    public List<Long> findByFbAccountIdAndTime(Long fbAccountId, String beginTime, String endTime) {
        String sql = "select ad_group_id from ad_group where fb_account_id =? and created_time >= ? and created_time <= ?";
        return getJdbcTemplate().queryForList(sql, new Object[]{fbAccountId, beginTime, endTime}, Long.class);
    }

    @Override
    public  List<Long> findByFbConnectObjectIdAndTime(Long fbConnObjId, String beginTime, String endTime) {
        String sql = "select ad_group_id from ad_group where fb_connection_object_id =? and created_time >= ? and created_time <= ?";
        return getJdbcTemplate().queryForList(sql, new Object[]{fbConnObjId, beginTime, endTime}, Long.class);
    }


    /**
     * 根据adGroupId从缓存中查询单价信息
     *
     * @param adGroupId 广告ID
     * @return 查询到的单价信息
     */
    @Override
    public VpiInfo findVpiFromCache(Long adGroupId) {
        AdGroup adGroup = findByAdGroupId(adGroupId);
        if (null == adGroup || null == adGroup.getProductConfId() || null == adGroup.getFbConnectionObjectId() || StringUtils.isEmpty(adGroup.getTargeting())) {
            return null;
        }
        VpiInfo vpiInfo = getPriceNew(adGroupId, adGroup.getProductConfId(), adGroup.getFbConnectionObjectId(), adGroup.getTargeting());
        return vpiInfo;
    }

    @Override
    public void updateAdGroupStatus(Long adGroupId, String adgroupStatus) {
        String sql = "UPDATE ad_group SET  adgroup_status = ?,local_updated_time = ?  WHERE ad_group_id=?";
        getJdbcTemplate().update(sql, new Object[]{adgroupStatus, System.currentTimeMillis(),adGroupId});
    }

    @Override
    public void updateAdCreativeId(Long adGroupId, Long adCreativeId) {
        String sql = "UPDATE ad_group SET ad_creative_id = ? WHERE ad_group_id=?";
        getJdbcTemplate().update(sql, new Object[]{adCreativeId, adGroupId});
    }

    @Override
    public void updateEcTemplateMaterielId(Long adGroupId, Long ecTemplateMaterielId) {
        String sql = "UPDATE ad_group SET ec_template_materiel_id = ? WHERE ad_group_id=?";
        getJdbcTemplate().update(sql, new Object[]{ecTemplateMaterielId, adGroupId});
    }
}
